local db = require("asyncsql4lua").mssql

local function connect(host, port, user, password, database)
    local inner_fn = coroutine.wrap(
        function()
            db:connect(host, port, user, password, database)
        end
    )
    while true do
        if inner_fn() == nil then 
            break
        end
    end
end

local function execute(sql)
    local res
    local inner_fn = coroutine.wrap(
        function()
            res = db:execute(sql)
        end
    )
    while true do
        if inner_fn() == nil then 
            break
        end
    end
    return res
end

local function query(sql)
    local res
    local inner_fn = coroutine.wrap(
        function()
            res = db:query(sql)
        end
    )
    while true do
        if inner_fn() == nil then
            break
        end
    end
    return res
end

local function main()
    connect(
    "localhost",    -- host
    1433,           -- port
    "sa",           -- user
    "Password2025", -- password
    "master",       -- database,
    10
    )
    
    execute([[
        IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='users' AND xtype='U')
        CREATE TABLE users (
        id INT IDENTITY(1,1) PRIMARY KEY,
        name NVARCHAR(100) NOT NULL,
        age INT NOT NULL,
        email NVARCHAR(100) NOT NULL,
        created_at DATETIME DEFAULT GETDATE())
    ]])


    local res = execute([[
        INSERT INTO 
        users
        (name, age, email)
        VALUES
        ('Li', 20, 'li@gmail.com'),
        ('Shang', 21, 'shang@email.com'),
        ('Wu', 30, 'wu@163.com')
    ]])

    print("rows_affected: " .. res.rows_affected)

    local res = query("SELECT * FROM users")
    for _, user in pairs(res) do
        print(user.id, user.name, user.age, user.created_at)
    end
end
main()